﻿Imports System.Windows.Forms
Imports Microsoft.Office.Interop
Imports System.IO

Public Class Acad_ImportWizardDialog

    Class ColumnClass
        Public ExcelIndex As Integer
        Public GridName As String
        Public ExcelName As String
        Public FieldCode As Integer
        Public Period_ID As Integer
    End Class

    Class ExcelColumnClass
        Public Index As Integer
        Public Name As String
        Public FieldCode As Integer
        Public GridName As String
        Public Period_ID As Integer
    End Class

    Private Sub OK_Button_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Me.DialogResult = System.Windows.Forms.DialogResult.OK
        Me.Close()
    End Sub

    Private Sub Cancel_Button_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Me.DialogResult = System.Windows.Forms.DialogResult.Cancel
        Me.Close()
    End Sub

    Private Sub Acad_ImportWizardDialog_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'Hide the tabs
        TheTabs.DrawMode = System.Windows.Forms.TabDrawMode.OwnerDrawFixed
        TheTabs.ItemSize = New System.Drawing.Size(0, 1)
        TheTabs.SizeMode = System.Windows.Forms.TabSizeMode.Fixed
    End Sub

    Private Sub NextBut_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles NextBut.Click
        If TheTabs.SelectedIndex < TheTabs.TabCount - 1 Then
            TheTabs.SelectedIndex = TheTabs.SelectedIndex + 1
            Backbut.Enabled = True
        End If

        If TheTabs.SelectedIndex = TheTabs.TabCount - 1 Then
            NextBut.Enabled = False
        Else
            NextBut.Enabled = True
        End If
    End Sub

    Private Sub Backbut_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Backbut.Click
        If TheTabs.SelectedIndex <> 0 Then
            TheTabs.SelectedIndex = TheTabs.SelectedIndex - 1
            NextBut.Enabled = True
        End If


        If TheTabs.SelectedIndex = 0 Then
            Backbut.Enabled = False
        Else
            Backbut.Enabled = True
        End If
    End Sub

    Private Sub CancelBut_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CancelBut.Click
        Me.Close()
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        If ExcelFileDialog.ShowDialog = Windows.Forms.DialogResult.OK Then
            FileBox.Text = ExcelFileDialog.FileName
        End If
    End Sub

    Private Sub IgnoreRadio_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles IgnoreRadio.CheckedChanged
        ExplainBlankRadios()
    End Sub

    Private Sub ExplainBlankRadios()
        Dim str As String = ""

        If IgnoreRadio.Checked Then
            str = "Selecting this option will ignore any blank fields in the Excel document.  This means that any data currently sitting in the database will not be overwritten by the blank and will be kept as the current value."
        End If

        If BlankRadio.Checked Then
            str = "Selecting this option will write blanks back to the database.  This means that all blank fields in the Excel document will overwrite any current data in the database (if there are any)."
        End If

        If ZeroRadio.Checked Then
            str = "Selecting this option will convert all blanks to zero.  This means that all blank fields in the Excel document will first be converted to a zero and then will overwrite any current data in the database (if there are any)."
        End If

        BlankExplainLabel.Text = str
    End Sub

    Private Sub BlankRadio_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BlankRadio.CheckedChanged
        ExplainBlankRadios()
    End Sub

    Private Sub ZeroRadio_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ZeroRadio.CheckedChanged
        ExplainBlankRadios()
    End Sub

    Private Sub AnalyzeFile()
        ButtonPanel.Enabled = False
        StartBut.Enabled = False
        AnalyzeBar.Value = 0

        Dim ESTAB As Boolean = False
        Dim UPIN As Boolean = False

        'xl stuff
        Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet
        Dim misValue As Object = System.Reflection.Missing.Value
        'Dim xlRng As Excel.Range

        Dim FoundSheet As Boolean = False
        Dim SheetNames As New List(Of String)
        Dim SheetName As String = ""

        Dim Columns As New List(Of ColumnClass)
        Dim ExcelColumns As New List(Of ExcelColumnClass)
        '  Dim GridColumn As DataGridViewColumn

        Dim r, c As Integer
        Dim HeaderRow, UAIColumn, LastColumn, LastRow As Integer

        Dim UAIMissingList As New List(Of String)

        Dim sql As String
        Dim TheView As DataView
        Dim rec As DataRowView

        xlApp = New Excel.ApplicationClass
        AnalyzeBar.Minimum = 0
        AnalyzeBar.Maximum = 5

        ProgressLabel.Text = "Opening Excel File (this could take a bit longer for bigger files)..."
        AnalyzeBar.Value = 0
        Me.Refresh()

        'lets first check to see if the excel file is password protected

        Try
            xlWorkBook = xlApp.Workbooks.Open(FileBox.Text, , True, , "NOT THE PASSWORD")
        Catch
            xlWorkBook = Nothing
        End Try

        If xlWorkBook Is Nothing Then
            Dim pw As String
            Me.Refresh()
            pw = InputBox("Please enter the password for the Excel file", "Excel password", "")
            Me.Refresh()

            Try
                xlWorkBook = xlApp.Workbooks.Open(FileBox.Text, , True, , pw)
                Me.Refresh()
            Catch ex As Exception
                xlWorkBook = Nothing
                ResetForm()
                xlApp.Quit()

                releaseObject(xlApp)
                releaseObject(xlWorkBook)
                releaseObject(xlWorkSheet)

                MessageBox.Show("I could not open the Excel file:  " & ex.Message, "File could not be opened", MessageBoxButtons.OK, MessageBoxIcon.Error)
                Exit Sub
            End Try
        Else
            '    Try
            ' xlWorkBook = xlApp.Workbooks.Open(FileBox.Text, , True)
            ' Catch ex As Exception
            ' ResetForm()
            ' xlApp.Quit()

            '            releaseObject(xlApp)
            '            releaseObject(xlWorkBook)
            '           releaseObject(xlWorkSheet)

            ' MessageBox.Show("I could not open the Excel file:  " & ex.Message, "File could not be opened", MessageBoxButtons.OK, MessageBoxIcon.Error)
            ' Exit Sub
            ' End Try

            'Me.Refresh()
        End If

        SheetNames.Clear()

        If xlWorkBook Is Nothing Then
            ResetForm()
            xlApp.Quit()

            releaseObject(xlApp)
            releaseObject(xlWorkBook)
            releaseObject(xlWorkSheet)
            MessageBox.Show("I could not open the file, make sure the file exists.", "Could not open file.", MessageBoxButtons.OK, MessageBoxIcon.Error)

            Exit Sub
        End If

        '----------------------Find the Sheet to use-----------------------
        Me.Refresh()
        ProgressLabel.Text = "Finding sheet..."
        AnalyzeBar.Value = 1
        Me.Refresh()

        For Each xlWorkSheet In xlWorkBook.Worksheets
            'see if you can figure out which sheet has got the data we need
            SheetNames.Add(xlWorkSheet.Name)

            If xlWorkSheet.Name = Acad_DatasetsFormV2.DatasetBox.Text Then
                FoundSheet = True
                SheetName = xlWorkSheet.Name
            End If
        Next

        If Not FoundSheet Then
            'could not find the sheet, so ask the user which sheet they want to use
            'load the box
            With Acad_WhichSheetDialog
                .TheLabel.Text = "I could not automaticaly determine which sheet to use.  Please indicate which sheet you would like me to use from the list below."
                .Text = "Which work sheet?"
                .SheetBox.Items.Clear()

                If SheetNames.Count = 0 Then
                    SheetName = ""
                    FoundSheet = False
                End If

                For r = 0 To SheetNames.Count - 1
                    .SheetBox.Items.Add(SheetNames(r))
                Next
            End With
            If Acad_WhichSheetDialog.ShowDialog = Windows.Forms.DialogResult.OK Then
                SheetName = Acad_WhichSheetDialog.SheetBox.SelectedItem
                FoundSheet = True
            Else
                'the user cancelled... why?  oh well... let us reset everything and start again!
                SheetName = ""
                FoundSheet = False
            End If
        End If

        'ok last check do we have a sheet or not
        If Not FoundSheet Then
            ResetForm()
            xlWorkBook.Close(False)
            xlApp.Quit()

            releaseObject(xlApp)
            releaseObject(xlWorkBook)
            releaseObject(xlWorkSheet)
            MessageBox.Show("The process was cancelled", "Cancelled", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Exit Sub
        End If


        '---------------Find UAI Column----------------------
        'we have a sheet!
        'BUT, does this sheet have at least a UAI?... lets find the column
        'Id say lets try up to 50 rows and columns .. surely it should be at least start in the first 50 rows?...

        ProgressLabel.Text = "Analysing Columns..."
        AnalyzeBar.Value = 2
        Me.Cursor = Cursors.WaitCursor
        Me.Refresh()

        xlWorkSheet = xlWorkBook.Sheets(SheetName)

        HeaderRow = 0
        UAIColumn = 0

        For r = 1 To 50
            For c = 1 To 50
                If Trim(xlWorkSheet.Cells(r, c).value) = "UAI" Then
                    HeaderRow = r
                    UAIColumn = c
                End If
            Next
        Next

        Me.Cursor = Cursors.Default
        Me.Refresh()

        If HeaderRow = 0 Or UAIColumn = 0 Then
            'There is no UAI column... oh dear  warn the user and exit.. but wait see if there is an ESTAB or UPIN column

            For r = 1 To 50
                For c = 1 To 50
                    If Trim(xlWorkSheet.Cells(r, c).value) = "UPIN" Then
                        HeaderRow = r
                        UAIColumn = c
                        UPIN = True
                    End If
                Next
            Next

            If UPIN = False Then
                For r = 1 To 50
                    For c = 1 To 50
                        If Trim(xlWorkSheet.Cells(r, c).value) = "ESTAB" Or Trim(xlWorkSheet.Cells(r, c).value) = "Establishment Number" Then
                            HeaderRow = r
                            UAIColumn = c
                            ESTAB = True
                        End If
                    Next
                Next
            End If
            

            If HeaderRow = 0 Or UAIColumn = 0 Then
                ResetForm()
                xlWorkBook.Close(False)
                xlApp.Quit()

                releaseObject(xlApp)
                releaseObject(xlWorkBook)
                releaseObject(xlWorkSheet)
                MessageBox.Show("I could not find the UAI, ESTAB or UPIN column.  Please make sure that a column with a title of UAI or ESTAB or UPIN exists", "No UAI, ESTAB or UPIN column found.", MessageBoxButtons.OK, MessageBoxIcon.Information)
                Exit Sub
            End If

        End If


        '----------------Identify Columns in Excel---------------------
        'We have the UAI column and the header row.  Now, lets start to see what other columns we can identify

        'lets load the columns list with the columns we are looking for
        Columns.Clear()
        Me.Cursor = Cursors.WaitCursor
        Me.Refresh()

        Dim DV As DataView
        DV = LoadDataView("select * from Acad_GAG_Templates where Formula = 'INPUT' and Dataset_Code = " & Acad_DatasetsFormV2.DatasetBox.SelectedValue.ToString)

        Dim row As DataRowView

        For Each row In DV
            Dim itm As New ColumnClass

            Dim FN As String

            FN = row("Field_Name")

            If FN.Contains("[YEAR]") Then
                FN = FN.Replace("[YEAR]", Period_Name(GetPeriodByNum(Acad_DatasetsFormV2.PeriodBox.zSelectedPeriod_ID, row("Period"))))
            End If

            If FN.ToString.Contains("[YEAR-1]") Then
                FN = FN.Replace("[YEAR-1]", Period_Name(GetPeriodByNum(Acad_DatasetsFormV2.PeriodBox.zSelectedPeriod_ID, row("Period") - 1)))
            End If

            itm.GridName = FN
            itm.FieldCode = row("Field_Code")
            itm.Period_ID = GetPeriodByNum(Acad_DatasetsFormV2.PeriodBox.zSelectedPeriod_ID, row("Period"))
            itm.ExcelIndex = 0
            itm.ExcelName = ""

            Columns.Add(itm)
        Next

        'For Each GridColumn In Acad_DatasetsFormV2.DataGrid.Columns
        '    If GridColumn.ReadOnly = False Then
        '        If Not GridColumn.Name.Contains("_Notes") Then
        '            If GridColumn.Visible = True Then

        '                Dim itm As New ColumnClass

        '                itm.GridName = GridColumn.Name
        '                itm.FieldCode = Acad_DatasetsForm.FieldCode(GridColumn.Name)
        '                itm.Period_ID = Acad_DatasetsForm.FieldPeriod(GridColumn.Name)
        '                itm.ExcelIndex = 0
        '                itm.ExcelName = ""

        '                Columns.Add(itm)

        '            End If
        '        End If
        '    End If
        'Next

        'lets find as many as we can from excel
        If Columns.Count = 0 Then
            Me.Cursor = Cursors.Default
            Me.Refresh()

            'something really strange... why are there no columns?  oh well, exit
            ResetForm()
            xlWorkBook.Close(False)
            xlApp.Quit()

            releaseObject(xlApp)
            releaseObject(xlWorkBook)
            releaseObject(xlWorkSheet)
            MessageBox.Show("No columns were found.", "Very strange!", MessageBoxButtons.OK, MessageBoxIcon.Information)
        End If

        'lets see where the columns end in Excel
        Dim check As Integer = 0
        c = 0

        ExcelColumns.Clear()
        Dim header As String

        While check <= 5
            c = c + 1
            Dim itm As New ExcelColumnClass

            If xlWorkSheet.Cells(HeaderRow, c).value Is Nothing Then
                check = check + 1
            Else

                'ignore the standard columns

                header = xlWorkSheet.Cells(HeaderRow, c).value
                header = header.Replace(Environment.NewLine, " ")
                header = header.Replace(vbCrLf, " ")

                header = header.Replace(Chr(10), " ")

                Select Case header
                    Case "UAI"
                    Case "Local Authority"
                    Case "Provider Name"
                    Case "UPIN"
                    Case "ESTAB"
                    Case "Establishment Number"
                    Case Else
                        itm.Index = c
                        itm.Name = header
                        itm.FieldCode = 0
                        itm.GridName = ""
                        ExcelColumns.Add(itm)
                End Select
            End If
        End While

        LastColumn = c
        AnalyzeBar.Value = 3
        For r = 0 To Columns.Count - 1
            'find where this column is in excel (if at all)
            For c = 0 To ExcelColumns.Count - 1
                If ExcelColumns(c).Name = Columns(r).GridName Then
                    Columns(r).ExcelName = ExcelColumns(c).Name
                    Columns(r).ExcelIndex = ExcelColumns(c).Index
                    ExcelColumns(c).FieldCode = Columns(r).FieldCode
                    ExcelColumns(c).GridName = Columns(r).GridName
                    ExcelColumns(c).Period_ID = Columns(r).Period_ID
                End If
            Next
        Next

        'report back to the user what has been found
        Me.Cursor = Cursors.Default
        Me.Refresh()

        With Acad_ImportColumnsDialog
            .ColumnsGrid.Rows.Clear()

            'lets add the list to the combobox

            'lets build the sql using the columns from the grid instead..

            sql = ""


            For Each row In DV
                Dim FN As String

                FN = row("Field_Name")

                If FN.Contains("[YEAR]") Then
                    FN = FN.Replace("[YEAR]", Period_Name(GetPeriodByNum(Acad_DatasetsFormV2.PeriodBox.zSelectedPeriod_ID, row("Period"))))
                End If

                If FN.ToString.Contains("[YEAR-1]") Then
                    FN = FN.Replace("[YEAR-1]", Period_Name(GetPeriodByNum(Acad_DatasetsFormV2.PeriodBox.zSelectedPeriod_ID, row("Period") - 1)))
                End If


                sql = sql & "Select '" & row("Field_Code") & "P" & GetPeriodByNum(Acad_DatasetsFormV2.PeriodBox.zSelectedPeriod_ID, row("Period")) & "' as Field_Code, '" & FN & "' as Field_Name, 1 as Field_Order UNION ALL "
            Next

            'For c = 0 To Acad_DatasetsFormV2.Fields.Count - 1
            '    sql = sql & "Select '" & Acad_DatasetsForm.Fields(c).FieldCode & "P" & Acad_DatasetsForm.Fields(c).Period_ID & "' as Field_Code, '" & Acad_DatasetsForm.Fields(c).FieldName & "' as Field_Name, 1 as Field_Order UNION ALL "
            'Next

            sql = sql & "select '0' as Field_Code, 'Ignore this column' as Field_Name, 0 as Field_Order order by Field_order"

            'sql = "select Field_Code, replace(Field_Name,'[YEAR]','" & Acad_DatasetsForm.PeriodBox.Text & "') as Field_Name, Field_order from dbo.Acad_GAG_Templates where Formula = 'INPUT' and Dataset_Code = " & Acad_DatasetsForm.DatasetBox.SelectedValue & _
            '   " union all select 0 as Field_Code, 'Ignore this column' as Field_name, 0 as Field_order order by Field_order"

            .StoreColumn.DataSource = LoadDataView(sql)
            .StoreColumn.DisplayMember = "Field_Name"
            .StoreColumn.ValueMember = "Field_Code"

            For c = 0 To ExcelColumns.Count - 1

                .ColumnsGrid.Rows.Add()
                .ColumnsGrid.Item(0, c).Value = ExcelColumns(c).Name

                If ExcelColumns(c).FieldCode <> 0 Then
                    .ColumnsGrid.Item(1, c).Value = ExcelColumns(c).FieldCode & "P" & ExcelColumns(c).Period_ID
                Else
                    .ColumnsGrid.Item(1, c).Value = "0"
                End If
            Next

            If .ShowDialog() = Windows.Forms.DialogResult.OK Then
                'place the selections back into the list
                For c = 0 To ExcelColumns.Count - 1
                    If .ColumnsGrid.Item(1, c).Value = "0" Then
                        ExcelColumns(c).FieldCode = 0
                        ExcelColumns(c).Period_ID = 0
                    Else
                        ExcelColumns(c).FieldCode = Mid(.ColumnsGrid.Item(1, c).Value, 1, 3)
                        ExcelColumns(c).Period_ID = Mid(.ColumnsGrid.Item(1, c).Value, 5, 7)
                    End If
                    
                Next

            Else
                'the user cancelled...
                ResetForm()
                xlWorkBook.Close(False)
                xlApp.Quit()

                releaseObject(xlApp)
                releaseObject(xlWorkBook)
                releaseObject(xlWorkSheet)
                Exit Sub
            End If

        End With

        '-------------------Analyze UAI's-------------------
        'now lets analyze the rows to see if we can find a UAI or ESTAB for all the ones in the excel file

        If Not ESTAB Then
            If Not UPIN Then
                ProgressLabel.Text = "Analysing UAIs..."
            Else
                ProgressLabel.Text = "Analysing UPINs..."
            End If

        Else
            ProgressLabel.Text = "Analysing ESTABs..."
        End If

        AnalyzeBar.Value = 4
        Me.Refresh()

        UAIMissingList.Clear()

        r = HeaderRow

        Me.Cursor = Cursors.WaitCursor
        MainForm.Refresh()

        check = 0
        While check <= 5
            r = r + 1
            If xlWorkSheet.Cells(r, UAIColumn).value Is Nothing Then
                check = check + 1
            Else
                If Not ESTAB Then
                    If Not UPIN Then
                        If UAIExists(Trim(xlWorkSheet.Cells(r, UAIColumn).value)) = False Then
                            UAIMissingList.Add(Trim(xlWorkSheet.Cells(r, UAIColumn).value))
                        End If
                    Else
                        'UPIN
                        If UPINExists(Trim(xlWorkSheet.Cells(r, UAIColumn).value)) = False Then
                            UAIMissingList.Add(Trim(xlWorkSheet.Cells(r, UAIColumn).value))
                        End If
                    End If
                    
                Else
                    If EstabExists(Trim(xlWorkSheet.Cells(r, UAIColumn).value)) = False Then
                        UAIMissingList.Add(Trim(xlWorkSheet.Cells(r, UAIColumn).value))
                    End If
                End If
                
            End If

        End While

        LastRow = r

        Me.Cursor = Cursors.Default
        Me.Refresh()

        If UAIMissingList.Count > 0 Then
            'some UAI's/ESTABS cannot be found... lets tell the user and ask what they want us to do about it
            With Acad_WhichSheetDialog
                .SheetBox.Items.Clear()
                If Not ESTAB Then
                    If Not UPIN Then
                        .TheLabel.Text = "These UAI's could not be found in The Store.  Click OK if you would like me to continue and ignore these UAI's.  Click Cancel if you would like to go back."
                        .Text = "Some UAI's could not be found"
                    Else
                        .TheLabel.Text = "These UPINs could not be found in The Store.  Click OK if you would like me to continue and ignore these UPINs.  Click Cancel if you would like to go back."
                        .Text = "Some UPINs could not be found"
                    End If
                    
                Else
                    .TheLabel.Text = "These ESTAB's could not be found in The Store.  Click OK if you would like me to continue and ignore these ESTAB's.  Click Cancel if you would like to go back."
                    .Text = "Some ESTAB's could not be found"
                End If
                
                For r = 0 To UAIMissingList.Count - 1
                    .SheetBox.Items.Add(UAIMissingList(r))
                Next
                If .ShowDialog = Windows.Forms.DialogResult.Cancel Then
                    'the user cancelled...
                    ResetForm()
                    xlWorkBook.Close(False)
                    xlApp.Quit()

                    releaseObject(xlApp)
                    releaseObject(xlWorkBook)
                    releaseObject(xlWorkSheet)
                    Exit Sub
                End If
            End With
        End If

        '-------------------Import into Temp Table------------------
        'now we are ready to import.  But we wont actaully import it.  What we will do is import it into a temp table where the value is string, so that we can analyze it and see if the rows will actually import
        ProgressLabel.Text = "Reading values, this might take a while..."
        AnalyzeBar.Value = 0
        AnalyzeBar.Minimum = 0
        AnalyzeBar.Maximum = LastRow
        Me.Cursor = Cursors.WaitCursor

        Me.Refresh()

        Dim TheValue As String
        Dim TheUAI As String
        Dim CellType As Object
        Dim ErrorMessage As String = ""

        sql = "delete from Acad_GAG_ImportData where User_Name = '" & CurrentUser.Name & "'"

        ErrorMessage = RunNonQuery(sql)

        If ErrorMessage <> "" Then
            MessageBox.Show(ErrorMessage & "  Click OK to continue.", "There was an Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End If

        Dim ImportValue As Boolean = False

        For r = HeaderRow + 1 To LastRow

            AnalyzeBar.Value = r
            Me.Refresh()

            For c = 0 To ExcelColumns.Count - 1

                If ExcelColumns(c).FieldCode <> 0 Then

                    CellType = xlWorkSheet.Cells(r, ExcelColumns(c).Index).value


                    If CellType = Nothing Then
                        TheValue = Nothing
                    Else
                        If CellType.GetType().ToString.Contains("Date") Then
                            TheValue = CellType.ToOADate - 2
                        Else
                            TheValue = CellType
                        End If
                    End If
                    

                    TheUAI = xlWorkSheet.Cells(r, UAIColumn).value

                    'what to do with the value
                    If TheValue Is Nothing Then
                    Else
                        TheValue = RemoveBadChars(TheValue)
                    End If

                    If IgnoreRadio.Checked Then
                        If Trim(TheValue) = "" Then
                            ImportValue = False
                        Else
                            TheValue = "'" & TheValue & "'"
                            ImportValue = True
                        End If
                    End If

                    If BlankRadio.Checked Then
                        If Trim(TheValue) = "" Then
                            ImportValue = True
                            TheValue = "null"
                        Else
                            TheValue = "'" & TheValue & "'"
                            ImportValue = True
                        End If
                    End If

                    If ZeroRadio.Checked Then
                        If Trim(TheValue) = "" Then
                            ImportValue = True
                            TheValue = "'0'"
                        Else
                            TheValue = "'" & TheValue & "'"
                            ImportValue = True
                        End If
                    End If


                    If ImportValue Then
                        If Trim(TheUAI) <> "" Then
                            sql = "insert into Acad_GAG_ImportData (Dataset_Code, Period_ID, UAI, Field_Code, User_Name, Value) " & _
                                "values (" & Acad_DatasetsFormV2.DatasetBox.SelectedValue & "," & ExcelColumns(c).Period_ID & ", '" & TheUAI & "', " & ExcelColumns(c).FieldCode & ", '" & CurrentUser.Name & "', " & TheValue & ")"
                            '"values ('" & CurrentUser.Name & "', '" & TheUAI & "', " & ExcelColumns(c).FieldCode & ", " & TheValue & ", " & ExcelColumns(c).Period_ID & ")"
                            ErrorMessage = RunNonQuery(sql)

                            If ErrorMessage <> "" Then
                                MessageBox.Show(ErrorMessage & "  Click OK to continue.", "There was an Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                            End If
                        End If
                    End If

                End If
            Next
        Next

        Me.Cursor = Cursors.Default
        xlWorkBook.Close(False)
        xlApp.Quit()

        releaseObject(xlApp)
        releaseObject(xlWorkBook)
        releaseObject(xlWorkSheet)

        '-----------------------Import Errors Preview---------------------------------
        With Acad_Import_PreviewDialog
            'start with testing UAI that do not exist
            If Not ESTAB Then
                If Not UPIN Then
                    sql = "Select * from " & _
                    "(select distinct i.UAI, p.Provider_Name from Acad_GAG_ImportData i " & _
                    "left outer join Providers p on (p.UAI = i.UAI) where User_Name = '" & CurrentUser.Name & "') prov " & _
                    "where Provider_name is null"
                Else
                    sql = "Select * from " & _
                    "(select distinct i.UAI as UPIN, p.Provider_Name from Acad_GAG_ImportData i " & _
                    "left outer join Providers p on (cast(p.UPIN as nvarchar(10)) = i.UAI) where User_Name = '" & CurrentUser.Name & "') prov " & _
                    "where Provider_name is null"
                End If
                
            Else
                sql = "Select * from " & _
                "(select distinct i.UAI as ESTAB, p.Provider_Name from Acad_GAG_ImportData i " & _
                "left outer join all_Provider_Fields p on (cast(p.LA_Code as nvarchar(50)) + cast(p.Establishment_Number as nvarchar(50)) = i.UAI) where User_Name = '" & CurrentUser.Name & "') prov " & _
                "where Provider_name is null"
            End If
            

            TheView = LoadDataView(sql)

            .DataGrid.Rows.Clear()

            If TheView.Count <> 0 Then
                For r = 0 To TheView.Count - 1
                    .DataGrid.Rows.Add()

                    .DataGrid.Item(1, r).Value = "All fields"
                    If Not ESTAB Then
                        If Not UPIN Then
                            .DataGrid.Item(0, r).Value = TheView(r).Item("UAI").ToString
                            .DataGrid.Item(2, r).Value = "The UAI does not exist in The Store"
                        Else
                            .DataGrid.Item(0, r).Value = TheView(r).Item("UPIN").ToString
                            .DataGrid.Item(2, r).Value = "The UPIN does not exist in The Store"
                        End If
                    Else
                        .DataGrid.Item(0, r).Value = TheView(r).Item("ESTAB").ToString
                        .DataGrid.Item(2, r).Value = "The ESTAB does not exist in The Store"
                    End If

                Next
            End If

            'now test the value types and see if they are valid
            If Not ESTAB Then
                If Not UPIN Then
                    sql = "select t.*, i.UAI, case t.Type when 'YN' then case i.value when 'Y' then '0' else '1' end else i.Value end as Value, i.Period_ID from Acad_GAG_Templates t, " & _
                    "(select UAI, Field_Code, Value, Period_ID from Acad_GAG_ImportData where user_name = '" & CurrentUser.Name & "') i " & _
                    "where t.Field_Code = i.Field_Code and Dataset_Code = " & Acad_DatasetsFormV2.DatasetBox.SelectedValue & " order by Field_Order"
                Else
                    sql = "select t.*, i.UAI as UPIN, case t.Type when 'YN' then case i.value when 'Y' then '0' else '1' end else i.Value end as Value, i.Period_ID from Acad_GAG_Templates t, " & _
                    "(select UAI, Field_Code, Value, Period_ID from Acad_GAG_ImportData where user_name = '" & CurrentUser.Name & "') i " & _
                    "where t.Field_Code = i.Field_Code and Dataset_Code = " & Acad_DatasetsFormV2.DatasetBox.SelectedValue & " order by Field_Order"
                End If
                
            Else
                sql = "select t.*, i.UAI as ESTAB, case t.Type when 'YN' then case i.value when 'Y' then '0' else '1' end else i.Value end as Value, i.Period_ID from Acad_GAG_Templates t, " & _
                "(select UAI, Field_Code, Value, Period_ID from Acad_GAG_ImportData where user_name = '" & CurrentUser.Name & "') i " & _
                "where t.Field_Code = i.Field_Code and Dataset_Code = " & Acad_DatasetsFormV2.DatasetBox.SelectedValue & " order by Field_Order"
            End If
            

            TheView = LoadDataView(sql)

            r = .DataGrid.Rows.Count - 1
            For Each rec In TheView

                Dim Reason As String = ""
                If Not IsDBNull(rec("Value")) Then
                    If rec("Type") = "YN" Then
                        'should have already been converted
                    Else
                        Reason = TestValue(rec("Value"), rec("Type"))
                    End If
                End If

                If Reason <> "" Then

                    r = r + 1
                    .DataGrid.Rows.Add()
                    If Not ESTAB Then
                        If Not UPIN Then
                            .DataGrid.Item(0, r).Value = rec("UAI").ToString
                        Else
                            .DataGrid.Item(0, r).Value = rec("UPIN").ToString
                        End If

                    Else
                        .DataGrid.Item(0, r).Value = rec("ESTAB").ToString
                    End If

                    .DataGrid.Item(1, r).Value = rec("Field_name").ToString
                    .DataGrid.Item(2, r).Value = Reason
                    .DataGrid.Item(3, r).Value = rec("Field_Code").ToString
                    .DataGrid.Item(4, r).Value = rec("Period_ID").ToString
                End If
            Next

            If .DataGrid.Rows.Count <> 0 Then
                If .ShowDialog() <> Windows.Forms.DialogResult.OK Then
                    'the user is not happy... lets exit
                    ResetForm()
                    Exit Sub
                End If

                'delete the records from the temp table that we are not going to import
                ProgressLabel.Text = "Preparing data..."
                AnalyzeBar.Value = 0
                AnalyzeBar.Minimum = 0
                AnalyzeBar.Maximum = .DataGrid.Rows.Count
                AnalyzeBar.Step = 1
                Me.Refresh()

                For r = 0 To .DataGrid.Rows.Count - 1
                    AnalyzeBar.PerformStep()

                    If .DataGrid.Item(1, r).Value = "All fields" Then
                        sql = "delete from Acad_GAG_ImportData where User_name = '" & CurrentUser.Name & "' and UAI = '" & .DataGrid.Item(0, r).Value & "'"
                        ErrorMessage = RunNonQuery(sql)

                        If ErrorMessage <> "" Then
                            MessageBox.Show(ErrorMessage & "  Click OK to continue.", "There was an Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                        End If
                    Else
                        sql = "delete from Acad_GAG_ImportData where User_name = '" & CurrentUser.Name & "' and UAI = '" & .DataGrid.Item(0, r).Value & "' and Field_Code = " & .DataGrid.Item(3, r).Value & " and Period_ID = " & .DataGrid.Item(4, r).Value
                        ErrorMessage = RunNonQuery(sql)

                        If ErrorMessage <> "" Then
                            MessageBox.Show(ErrorMessage & "  Click OK to continue.", "There was an Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                        End If
                    End If
                Next

            End If

        End With

        '--------------------Transfer all the values to the actual data table--------------------
        If MessageBox.Show("I'm now ready to apply the imported data, click OK to continue", "Ready", MessageBoxButtons.OKCancel, MessageBoxIcon.Information) = Windows.Forms.DialogResult.OK Then
            'lets do it!
            ProgressLabel.Text = "Applying imported data..."
            AnalyzeBar.Value = 0
            AnalyzeBar.Minimum = 0
            AnalyzeBar.Maximum = 2
            AnalyzeBar.Step = 1
            Me.Cursor = Cursors.WaitCursor

            Me.Refresh()
            AnalyzeBar.PerformStep()

            'set records first to history
            If Not ESTAB Then
                If Not UPIN Then
                    sql = "update Acad_GAG_Data " & _
                    "set Status = 'History' from " & _
                    "(select t.Dataset_CODe, i.Period_ID, p.RID, t.Field_Code, " & Now.ToOADate - 2 & " as TheDate, '" & CurrentUser.Name & "' as User_Name, 'Approved' As Status " & _
                    ", case t.Type when 'YN' then case i.value when 'Y' then '0' else '1' end else i.Value end as Value  " & _
                    "from (select distinct dataset_Code, Field_Code, Type from Acad_GAG_Templates) t,  " & _
                    "(select UAI, Dataset_Code, Field_Code, Value, Period_ID from Acad_GAG_ImportData where user_name = '" & CurrentUser.Name & "' and dataset_code = " & Acad_DatasetsFormV2.DatasetBox.SelectedValue & ") i, " & _
                    "All_Provider_Fields p " & _
                    "where t.Field_Code = i.Field_Code and t.Dataset_Code = i.Dataset_Code " & _
                    "and p.UAI = i.UAI and p.Lead = 1) sub " & _
                    "where sub.Dataset_Code = Acad_GAG_Data.Dataset_Code " & _
                    "and sub.Period_ID = Acad_GAG_Data.Period_ID " & _
                    "and sub.RID = Acad_GAG_Data.RID " & _
                    "and sub.Field_Code = Acad_GAG_Data.Field_Code "
                Else
                    sql = "update Acad_GAG_Data " & _
                    "set Status = 'History' from " & _
                    "(select t.Dataset_CODe, i.Period_ID, p.RID, t.Field_Code, " & Now.ToOADate - 2 & " as TheDate, '" & CurrentUser.Name & "' as User_Name, 'Approved' As Status " & _
                    ", case t.Type when 'YN' then case i.value when 'Y' then '0' else '1' end else i.Value end as Value  " & _
                    "from (select distinct dataset_Code, Field_Code, Type from Acad_GAG_Templates) t,  " & _
                    "(select UAI, Dataset_Code, Field_Code, Value, Period_ID from Acad_GAG_ImportData where user_name = '" & CurrentUser.Name & "' and dataset_code = " & Acad_DatasetsFormV2.DatasetBox.SelectedValue & ") i, " & _
                    "All_Provider_Fields p " & _
                    "where t.Field_Code = i.Field_Code and t.Dataset_Code = i.Dataset_Code " & _
                    "and cast(p.UPIN as nvarchar(10)) = i.UAI and p.Lead = 1) sub " & _
                    "where sub.Dataset_Code = Acad_GAG_Data.Dataset_Code " & _
                    "and sub.Period_ID = Acad_GAG_Data.Period_ID " & _
                    "and sub.RID = Acad_GAG_Data.RID " & _
                    "and sub.Field_Code = Acad_GAG_Data.Field_Code "
                End If
                

            Else
                sql = "update Acad_GAG_Data " & _
                    "set Status = 'History' from " & _
                    "(select t.Dataset_CODe, i.Period_ID, p.RID, t.Field_Code, " & Now.ToOADate - 2 & " as TheDate, '" & CurrentUser.Name & "' as User_Name, 'Approved' As Status " & _
                    ", case t.Type when 'YN' then case i.value when 'Y' then '0' else '1' end else i.Value end as Value  " & _
                    "from (select distinct dataset_Code, Field_Code, Type from Acad_GAG_Templates) t,  " & _
                    "(select UAI, Dataset_Code, Field_Code, Value, Period_ID from Acad_GAG_ImportData where user_name = '" & CurrentUser.Name & "' and dataset_code = " & Acad_DatasetsFormV2.DatasetBox.SelectedValue & ") i, " & _
                    "All_Provider_Fields p " & _
                    "where t.Field_Code = i.Field_Code and t.Dataset_Code = i.Dataset_Code " & _
                    "and cast(p.LA_Code as nvarchar(50)) + cast(p.Establishment_Number as nvarchar(50)) = i.UAI and p.Lead = 1) sub " & _
                    "where sub.Dataset_Code = Acad_GAG_Data.Dataset_Code " & _
                    "and sub.Period_ID = Acad_GAG_Data.Period_ID " & _
                    "and sub.RID = Acad_GAG_Data.RID " & _
                    "and sub.Field_Code = Acad_GAG_Data.Field_Code "

            End If
            
            ErrorMessage = RunNonQuery(sql)

            If ErrorMessage <> "" Then
                MessageBox.Show(ErrorMessage & "  Click OK to continue.", "There was an Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End If

            AnalyzeBar.PerformStep()

            'now add the records
            If Not ESTAB Then
                If Not UPIN Then
                    sql = "insert into Acad_GAG_Data " & _
                    "select t.Dataset_CODe, i.Period_ID, p.RID, t.Field_Code, " & Now.ToOADate - 2 & " as TheDate, '" & CurrentUser.Name & "' as User_Name, 'Approved' As Status " & _
                    ", case t.Type when 'YN' then case i.value when 'Y' then '0' else '1' end else i.Value end as Value  " & _
                    "from (select distinct dataset_Code, Field_Code, Type from Acad_GAG_Templates) t,  " & _
                    "(select UAI, Dataset_Code, Field_Code, Value, Period_ID from Acad_GAG_ImportData where user_name = '" & CurrentUser.Name & "' and dataset_code = " & Acad_DatasetsFormV2.DatasetBox.SelectedValue & ") i, " & _
                    "All_Provider_Fields p " & _
                    "where t.Field_Code = i.Field_Code  and t.Dataset_Code = i.Dataset_Code " & _
                    "and p.UAI = i.UAI and p.Lead = 1 "
                Else
                    sql = "insert into Acad_GAG_Data " & _
                    "select t.Dataset_CODe, i.Period_ID, p.RID, t.Field_Code, " & Now.ToOADate - 2 & " as TheDate, '" & CurrentUser.Name & "' as User_Name, 'Approved' As Status " & _
                    ", case t.Type when 'YN' then case i.value when 'Y' then '0' else '1' end else i.Value end as Value  " & _
                    "from (select distinct dataset_Code, Field_Code, Type from Acad_GAG_Templates) t,  " & _
                    "(select UAI, Dataset_Code, Field_Code, Value, Period_ID from Acad_GAG_ImportData where user_name = '" & CurrentUser.Name & "' and dataset_code = " & Acad_DatasetsFormV2.DatasetBox.SelectedValue & ") i, " & _
                    "All_Provider_Fields p " & _
                    "where t.Field_Code = i.Field_Code  and t.Dataset_Code = i.Dataset_Code " & _
                    "and cast(p.UPIN as nvarchar(10)) = i.UAI and p.Lead = 1 "
                End If
                
            Else
                sql = "insert into Acad_GAG_Data " & _
                    "select t.Dataset_CODe, i.Period_ID, p.RID, t.Field_Code, " & Now.ToOADate - 2 & " as TheDate, '" & CurrentUser.Name & "' as User_Name, 'Approved' As Status " & _
                    ", case t.Type when 'YN' then case i.value when 'Y' then '0' else '1' end else i.Value end as Value  " & _
                    "from (select distinct dataset_Code, Field_Code, Type from Acad_GAG_Templates) t,  " & _
                    "(select UAI, Dataset_Code, Field_Code, Value, Period_ID from Acad_GAG_ImportData where user_name = '" & CurrentUser.Name & "' and dataset_code = " & Acad_DatasetsFormV2.DatasetBox.SelectedValue & ") i, " & _
                    "All_Provider_Fields p " & _
                    "where t.Field_Code = i.Field_Code and t.Dataset_Code = i.Dataset_Code " & _
                    "and cast(p.LA_Code as nvarchar(50)) + cast(p.Establishment_Number as nvarchar(50)) = i.UAI and p.Lead = 1 "
            End If
            
            ErrorMessage = RunNonQuery(sql)

            If ErrorMessage <> "" Then
                MessageBox.Show(ErrorMessage & "  Click OK to continue.", "There was an Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End If

            Me.Cursor = Cursors.Default
            Me.DialogResult = Windows.Forms.DialogResult.OK
            Me.Close()
        Else
            Me.DialogResult = Windows.Forms.DialogResult.Cancel
            Me.Close()
        End If

    End Sub

    Private Sub StartBut_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles StartBut.Click
        'just check to see if the file exists

        If Not File.Exists(FileBox.Text) Then
            MessageBox.Show("I can't seem to find the Excel file you selected.  Please review the file and try again.", "Excel file not found", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Exit Sub
        End If

        AnalyzeFile()
    End Sub

    Private Sub ResetForm()
        StartBut.Enabled = True
        ButtonPanel.Enabled = True
        NextBut.Enabled = False
        ProgressLabel.Text = ""
        AnalyzeBar.Value = 0
    End Sub

    Private Function UAIExists(ByVal UAI As String) As Boolean
        Dim sql As String
        Dim TheView As DataView

        sql = "select * from providers where UAI = '" & RemoveBadChars(UAI) & "'"
        TheView = LoadDataView(sql)

        If TheView.Count = 0 Then
            Return False
        Else
            Return True
        End If

    End Function

    Private Function UPINExists(ByVal UPIN As String) As Boolean
        Dim sql As String
        Dim TheView As DataView

        sql = "select * from providers where UPIN = '" & RemoveBadChars(UPIN) & "'"
        TheView = LoadDataView(sql)

        If TheView.Count = 0 Then
            Return False
        Else
            Return True
        End If

    End Function

    Private Function EstabExists(ByVal ESTAB As String) As Boolean
        Dim sql As String
        Dim TheView As DataView

        sql = "select UPIN from All_Provider_Fields where cast(LA_Code as nvarchar(50)) + cast(Establishment_Number as nvarchar(50)) = '" & RemoveBadChars(ESTAB) & "'"
        TheView = LoadDataView(sql)

        If TheView.Count = 0 Then
            Return False
        Else
            Return True
        End If

    End Function

    Private Function TestValue(ByVal TheValue As Object, ByVal Type As String) As String
        Dim AInt As Integer
        Dim ADouble As Double
        Dim YN As String
        Dim Reason As String = ""
        Dim ADate As DateTime

        Try
            Select Case Type
                Case "INT"
                    Reason = TheValue & " is not a valid integer"
                    AInt = TheValue

                Case "MONEY"
                    Reason = TheValue & " is not a valid number"
                    ADouble = TheValue

                Case "YN"
                    Reason = TheValue & " should be either Y or N"
                    YN = TheValue

                    If TheValue.ToString.ToUpper <> "Y" Then
                        If TheValue.ToString.ToUpper <> "N" Then
                            Reason = TheValue & " should be either Y or N"
                        Else
                            Reason = ""
                        End If
                    Else
                        Reason = ""
                    End If
                Case "DP1"
                    Reason = TheValue & " is not a valid number"
                    ADouble = TheValue

                Case "DP2"
                    Reason = TheValue & " is not a valid number"
                    ADouble = TheValue

                Case "DP3"
                    Reason = TheValue & " is not a valid number"
                    ADouble = TheValue

                Case "DP4"
                    Reason = TheValue & " is not a valid number"
                    ADouble = TheValue

                Case "DP5"
                    Reason = TheValue & " is not a valid number"
                    ADouble = TheValue

                Case "DP6"
                    Reason = TheValue & " is not a valid number"
                    ADouble = TheValue

                Case "PERCENT"
                    Reason = TheValue & " is not a valid number"
                    ADouble = TheValue

                Case "DATE"
                    Reason = TheValue & " is not a valid date"
                    ADate = TheValue

                Case Else
                    Return Reason
            End Select
        Catch ex As Exception
            Return Reason
            Exit Function
        End Try

        Reason = ""
        Return Reason
    End Function

    Private Function FindRID(ByVal UAI As String) As String
        Dim sql As String
        Dim TheView As DataView

        sql = "Select RID from all_provider_Fields where UAI = '" & UAI & "'"
        TheView = LoadDataView(sql)

        If TheView.Count = 0 Then
            Return ""
        Else
            Return TheView(0).Item("RID").ToString
        End If


    End Function
End Class
